import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;


public class DataHandler implements IDataHandler
{
	// Class used to add and read data to/from the excel sheet
	// The class is also responsible for updating the data onto the item table accordingly
	
	/*
	 *  Pattern for the excel sheet is in the following format:
	 *  
	 *  	1. Item Name - String
	 *  	2. Item Description - String
	 *  	3. Item Value - Numeric
	 *  	4. Item Date (format: dd/mm/yyyy) - Date
	 *  	5. Item Priority - String
	 *  	6. Item Recurrence - Boolean/String
	 *  	7. Item Recurrence Nature - String
	 */
	
	private volatile ITable itemTable = null;
	private HSSFWorkbook myBook = null;
	private HSSFSheet mySheet = null;
	
	public DataHandler(ITable iTable)
	{
		this.itemTable = iTable;
		
		//prepareExcelSheet();
	}
	
	public void prepareExcelSheet()
	{
		// Try to load an already existing excel sheet
		
		FileInputStream fis = null;
		
		try {
			fis = new FileInputStream("./Personal-Finance-Sheet.xls");
			
			myBook = new HSSFWorkbook(fis);
			mySheet = myBook.getSheetAt(0);
			
			retrieveExcelData();
		} catch (Exception e) {					
			System.out.println("Warning !! .. No Excel file exists ... Creating a new file !!");
			
			createExcelSheet();
		} finally {
			if (fis != null)
				try {
					fis.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
	}
	
	private void createExcelSheet()
	{
		myBook = new HSSFWorkbook();
		mySheet = myBook.createSheet("Financial-Sheet1");
		HSSFRow myRow = mySheet.createRow(0);
		
		String[] headingList = {"Item Name", "Item Description", "Item Value", "Item Date", "Item Priority", 
				"Item Recurrence","Item Recurrence Nature"};
		
		for(int i=0;i<headingList.length;i++)
		{
			HSSFCell newCell = myRow.createCell(i);
			newCell.setCellValue(headingList[i]);
		}
		
		FileOutputStream fos = null;
		
		try {
			fos = new FileOutputStream("./Personal-Finance-Sheet.xls");
			
			myBook.write(fos);
			
			fos.flush();
			fos.close();
		} catch (Exception e) {			
			e.printStackTrace();
		}
	}
	
	public void addFinancialItem(IFinancial_Item newItem)
	{
		// Function to add the new financial item to both the ITable and the excel sheet
		
		// Code to add the data to the ITable
		
		itemTable.addItem(newItem);
		
		// Code to add the data to the excel sheet
		
		FileOutputStream fos = null;
		
		try {
				fos = new FileOutputStream("./Personal-Finance-Sheet.xls");
			
				HSSFRow newRow = mySheet.createRow(mySheet.getLastRowNum() + 1);
				
				HSSFCell cell_Name = newRow.createCell(0);
				cell_Name.setCellValue(newItem.getItemName());	// Saving the Name
				
				HSSFCell cell_Desc = newRow.createCell(1);
				cell_Desc.setCellValue(newItem.getItemDesc()); // Saving the Desc
				
				HSSFCell cell_Value = newRow.createCell(2);
				cell_Value.setCellValue(newItem.getItemValue());	// Saving the Value
				
				HSSFCell cell_Date = newRow.createCell(3);
				cell_Date.setCellValue(newItem.getDate());			// Saving the Date
				
				HSSFCell cell_Priority = newRow.createCell(4);
				cell_Priority.setCellValue(newItem.getItemPriority());		// Saving the Priority
				
				HSSFCell cell_Recurrence = newRow.createCell(5);
				cell_Recurrence.setCellValue(newItem.getRecurrence());		// Saving the Recurrence
				
				HSSFCell cell_RecNature = newRow.createCell(6);
				cell_RecNature.setCellValue(newItem.getRecurrenceNature());		// Saving the Recurrence Nature
				
				myBook.write(fos);
				
				fos.flush();
				fos.close();				
		} catch(Exception e) {
			e.printStackTrace();
			
			System.out.println("Error in locating excel file !!");
		}
	}
	
	private void retrieveExcelData()
	{
		// Method to retrieve data from excel sheet and transfer it to the ITable.
		
		Iterator<Row> rows = mySheet.rowIterator();
		
		while(rows.hasNext())
		{
			IFinancial_Item currentItem = null;
			
			String iName = "";
			String iDesc = "";
			Double iValue = 0.0;
			Calendar iDate = Calendar.getInstance();
			ItemPriority iPriority = null;
			Boolean recItem = null;
			ItemRecurrence recNature = null;
			
			HSSFRow row = (HSSFRow) rows.next();
			
			Iterator<Cell> cells = row.cellIterator();
			
			while(cells.hasNext())
			{
				HSSFCell cell = (HSSFCell) cells.next();
				
				int cellColumnIndex = cell.getColumnIndex();
				
				switch(cellColumnIndex)
				{
					case 0 :	iName = cell.getStringCellValue();
								break;
					
					case 1 :	iDesc = cell.getStringCellValue();
								break;
								
					case 2 :	iValue = cell.getNumericCellValue();
								break;
								
					case 3 :	Date tempDate = cell.getDateCellValue();
								iDate.setTime(tempDate);
								break;
								
					case 4 : 	String tempPriority = cell.getStringCellValue();
								iPriority = ItemPriority.valueOf(tempPriority);
								break;
								
					case 5 :	recItem = new Boolean(cell.getBooleanCellValue());
								break;
								
					case 6 :	String tempNature = cell.getStringCellValue();
								recNature = ItemRecurrence.valueOf(tempNature);
								break;
				}
			}
			
			currentItem = new Financial_Item(iName,iDesc,iValue,recItem.booleanValue(),recNature,iPriority);
			currentItem.setYear(iDate.get(Calendar.YEAR));
			currentItem.setMonth(iDate.get(Calendar.MONTH));
			currentItem.setDay_Of_Month(iDate.get(Calendar.DAY_OF_MONTH));
			
			this.itemTable.addItem(currentItem);
		}
	}
}
